package com.neuedu.jdbc.v2.dao;

import com.neuedu.jdbc.v1.entity.Emp;
import com.neuedu.jdbc.v2.entity.Dept;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DeptDao extends BaseDao<Dept> {
    @Override
    public List<Dept> selectList(String sql , Object ... params) {
        List list = new ArrayList();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql);

            //绑定变量
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }
            rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            while (rs.next()) {//循环行
                Dept dept = new Dept();
                //循环列
                dept.setDeptno(rs.getInt("deptno"));
                dept.setDname(rs.getString("dname"));
                dept.setLoc(rs.getString("loc"));

                list.add(dept);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.close(rs, stmt, conn);
        }

        return list;
    }

    @Override
    public Dept selectById(Integer id) {
        Dept dept = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement("select * from dept where deptno = ?");

            //绑定变量
            stmt.setObject(1,id);
            rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            if (rs.next()) {//循环行
                dept = new Dept();
                //循环列
                dept.setDeptno(rs.getInt("deptno"));
                dept.setDname(rs.getString("dname"));
                dept.setLoc(rs.getString("loc"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.close(rs, stmt, conn);
        }

        return dept;
    }

    @Override
    public int selectCount(String sql) {

        int count = 0;

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(" select count(1) c from  (  " + sql + ") tab ");
            //绑定变量
            rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            if (rs.next()) {//循环行
                count = rs.getInt("c");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }

        return count;
    }

    @Override
    public int update(String sql, Object... params) {
        int count = 0;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql.toString());

            //绑定变量
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }

            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }
        return count;
    }

    @Override
    public int deleteById(Integer id) {
        int count = 0;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "delete from dept where deptno = ? ";
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql.toString());

            //绑定变量
            stmt.setObject(1, id);

            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }
        return count;
    }
}
